﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.ComponentModel
Public Class Personas
    Inherits BDDatos
    Sub New()
        Me.Tabla = "Personas"
        Me.Vista = "VPersonas"
    End Sub
    Public CEDULA As String
    Public PRIMER_APELLIDO As String
    Public PRIMER_NOMBRE As String
    Public DIRECCION As String
    Public TELEFONO As String
    Public EMAIL As String
    Public SEXO As String
    Public TP As String
    Public LM As String
    Public DM As String
    Public FECHA_NACIMIENTO As String
    Public TIP_DOC As String
    Public ESTADO_CIVIL As String
    Public DPTO_NTO As String
    Public MCPIO_NTO As String
    Public RELIGION As String
    Public SEGUNDO_APELLIDO As String
    Public SEGUNDO_NOMBRE As String
    Public LUG_EXP_DOC As String
    Public FEC_EXP_DOC As String
    Public DPTO_RESIDENCIA As String
    Public MUN_RESIDENCIA As String
    Public CELULAR As String
    Public GRUPO_SANGUINEO As String
    Public FAX As String
    Public IDPK As String
    Public Function Insert() As String
        Me.Conectar()
        Me.ComenzarTransaccion()
        Try
            Me.querystring = "Insert into PERSONAS" & _
            "(CEDULA, PRIMER_APELLIDO, PRIMER_NOMBRE, DIRECCION, TELEFONO, EMAIL, SEXO, TP, LM, DM, FECHA_NACIMIENTO, TIP_DOC, ESTADO_CIVIL, DPTO_NTO, MCPIO_NTO, RELIGION, SEGUNDO_APELLIDO, SEGUNDO_NOMBRE, LUG_EXP_DOC, FEC_EXP_DOC, DPTO_RESIDENCIA, MUN_RESIDENCIA, CELULAR, GRUPO_SANGUINEO, FAX)" & _
            "Values" & _
            "(:CEDULA, :PRIMER_APELLIDO, :PRIMER_NOMBRE, :DIRECCION, :TELEFONO, :EMAIL, :SEXO, :TP, :LM, :DM, :FECHA_NACIMIENTO, :TIP_DOC, :ESTADO_CIVIL, :DPTO_NTO, :MCPIO_NTO, :RELIGION, :SEGUNDO_APELLIDO, :SEGUNDO_NOMBRE, :LUG_EXP_DOC, :FEC_EXP_DOC, :DPTO_RESIDENCIA, :MUN_RESIDENCIA, :CELULAR, :GRUPO_SANGUINEO, :FAX)"
            Me.CrearComando(querystring)
            Me.AsignarParametroCadena(":CEDULA", Me.CEDULA)
            Me.AsignarParametroCadena(":PRIMER_APELLIDO", UCase(Me.PRIMER_APELLIDO))
            Me.AsignarParametroCadena(":PRIMER_NOMBRE", UCase(PRIMER_NOMBRE))
            Me.AsignarParametroCadena(":DIRECCION", UCase(Me.DIRECCION))
            Me.AsignarParametroCadena(":TELEFONO", Me.TELEFONO)
            Me.AsignarParametroCadena(":EMAIL", Me.EMAIL)
            Me.AsignarParametroCadena(":SEXO", Me.SEXO)
            Me.AsignarParametroCadena(":TP", TP)
            Me.AsignarParametroCadena(":LM", Me.LM)
            Me.AsignarParametroCadena(":DM", Me.DM)
            Me.AsignarParametroCadena(":FECHA_NACIMIENTO", Me.FECHA_NACIMIENTO)
            Me.AsignarParametroCadena(":TIP_DOC", Me.TIP_DOC)
            Me.AsignarParametroCadena(":ESTADO_CIVIL", ESTADO_CIVIL)
            Me.AsignarParametroCadena(":DPTO_NTO", Me.DPTO_NTO)
            Me.AsignarParametroCadena(":MCPIO_NTO", Me.MCPIO_NTO)
            Me.AsignarParametroCadena(":RELIGION", Me.RELIGION)
            Me.AsignarParametroCadena(":SEGUNDO_APELLIDO", UCase(Me.SEGUNDO_APELLIDO))
            Me.AsignarParametroCadena(":SEGUNDO_NOMBRE", UCase(SEGUNDO_NOMBRE))
            Me.AsignarParametroCadena(":LUG_EXP_DOC", UCase(Me.LUG_EXP_DOC))
            Me.AsignarParametroCadena(":FEC_EXP_DOC", Me.FEC_EXP_DOC)
            Me.AsignarParametroCadena(":DPTO_RESIDENCIA", Me.DPTO_RESIDENCIA)
            Me.AsignarParametroCadena(":MUN_RESIDENCIA", Me.MUN_RESIDENCIA)
            Me.AsignarParametroCadena(":CELULAR", Me.CELULAR)
            Me.AsignarParametroCadena(":GRUPO_SANGUINEO", Me.GRUPO_SANGUINEO)
            Me.AsignarParametroCadena(":FAX", Me.FAX)
            Me.num_reg = EjecutarComando()
            Me.Msg = "Se realizo la Operacion Exitosamente - " + num_reg.ToString + " Filas Afectadas"
            Me.lErrorG = False
            Me.ConfirmarTransaccion()
        Catch ex As Exception
            CancelarTransaccion()
            Me.lErrorG = True
            Me.Msg = "Err: " + ex.Message
        Finally
            Me.Desconectar()
        End Try
        Return Msg
    End Function
    Public Function Update() As String
        Me.Conectar()
        Me.ComenzarTransaccion()
        Try
            Me.querystring = "Update PERSONAS set CEDULA=:CEDULA, PRIMER_APELLIDO=:PRIMER_APELLIDO, PRIMER_NOMBRE=:PRIMER_NOMBRE, DIRECCION=:DIRECCION, " & _
            "TELEFONO=:TELEFONO, EMAIL=:EMAIL, SEXO=:SEXO, TP=:TP, LM=:LM, DM=:DM, FECHA_NACIMIENTO=:FECHA_NACIMIENTO, TIP_DOC=:TIP_DOC, ESTADO_CIVIL=:ESTADO_CIVIL, " & _
            "DPTO_NTO=:DPTO_NTO, MCPIO_NTO=:MCPIO_NTO, RELIGION=:RELIGION, SEGUNDO_APELLIDO=:SEGUNDO_APELLIDO, SEGUNDO_NOMBRE=:SEGUNDO_NOMBRE, " & _
            "LUG_EXP_DOC=:LUG_EXP_DOC, FEC_EXP_DOC=:FEC_EXP_DOC, DPTO_RESIDENCIA=:DPTO_RESIDENCIA, MUN_RESIDENCIA=:MUN_RESIDENCIA, CELULAR=:CELULAR, " & _
            "GRUPO_SANGUINEO=:GRUPO_SANGUINEO, FAX=:FAX WHERE CEDULA=:CEDULAPK"
            Me.CrearComando(querystring)
            Me.AsignarParametroCadena(":CEDULA", Me.CEDULA)
            Me.AsignarParametroCadena(":PRIMER_APELLIDO", Me.PRIMER_APELLIDO)
            Me.AsignarParametroCadena(":PRIMER_NOMBRE", PRIMER_NOMBRE)
            Me.AsignarParametroCadena(":DIRECCION", Me.DIRECCION)
            Me.AsignarParametroCadena(":TELEFONO", Me.TELEFONO)
            Me.AsignarParametroCadena(":EMAIL", Me.EMAIL)
            Me.AsignarParametroCadena(":SEXO", Me.SEXO)
            Me.AsignarParametroCadena(":TP", TP)
            Me.AsignarParametroCadena(":LM", Me.LM)
            Me.AsignarParametroCadena(":DM", Me.DM)
            Me.AsignarParametroCadena(":FECHA_NACIMIENTO", Me.FECHA_NACIMIENTO)
            Me.AsignarParametroCadena(":TIP_DOC", Me.TIP_DOC)
            Me.AsignarParametroCadena(":ESTADO_CIVIL", ESTADO_CIVIL)
            Me.AsignarParametroCadena(":DPTO_NTO", Me.DPTO_NTO)
            Me.AsignarParametroCadena(":MCPIO_NTO", Me.MCPIO_NTO)
            Me.AsignarParametroCadena(":RELIGION", Me.RELIGION)
            Me.AsignarParametroCadena(":SEGUNDO_APELLIDO", Me.SEGUNDO_APELLIDO)
            Me.AsignarParametroCadena(":SEGUNDO_NOMBRE", SEGUNDO_NOMBRE)
            Me.AsignarParametroCadena(":LUG_EXP_DOC", Me.LUG_EXP_DOC)
            Me.AsignarParametroCadena(":FEC_EXP_DOC", Me.FEC_EXP_DOC)
            Me.AsignarParametroCadena(":DPTO_RESIDENCIA", Me.DPTO_RESIDENCIA)
            Me.AsignarParametroCadena(":MUN_RESIDENCIA", Me.MUN_RESIDENCIA)
            Me.AsignarParametroCadena(":CELULAR", Me.CELULAR)
            Me.AsignarParametroCadena(":GRUPO_SANGUINEO", Me.GRUPO_SANGUINEO)
            Me.AsignarParametroCadena(":FAX", Me.FAX)
            Me.AsignarParametroCadena(":CEDULAPK", Me.IDPK)
            Me.num_reg = EjecutarComando()
            Me.Msg = "Se realizo la Operacion Exitosamente - " + num_reg.ToString + " Filas Afectadas"
            Me.lErrorG = False
            Me.ConfirmarTransaccion()
        Catch ex As Exception
            CancelarTransaccion()
            Me.lErrorG = True
            Me.Msg = "Err: " + ex.Message
        Finally
            Me.Desconectar()
        End Try
        Return Msg
    End Function
    <DataObjectMethodAttribute(DataObjectMethodType.Select, True)> _
    Public Function GetPersonas(ByVal Buscar As String) As DataTable
        Me.Conectar()
        If Not String.IsNullOrEmpty(Buscar) Then
            Me.querystring = "Select * from VPERSONAS where cedula like :Buscar or upper(nombre) like :Buscar"
            CrearComando(querystring)
            Me.AsignarParametroCadena(":Buscar", UCase(Buscar))
            Me.AsignarParametroCadena(":Buscar", UCase(Buscar))
        Else
            Me.querystring = "Select * from VPERSONAS"
            CrearComando(querystring)
        End If
        Dim dt As DataTable = EjecutarConsultaDataTable()
        Me.Desconectar()
        Return dt
    End Function
    <DataObjectMethodAttribute(DataObjectMethodType.Select, True)> _
    Public Function GetbyPK(ByVal Cedula As String) As DataTable
        Me.Conectar()
        Me.querystring = "Select * from VPERSONAS where cedula=:cedula"
        CrearComando(querystring)
        Me.AsignarParametroCadena(":cedula", Cedula)
        Dim dt As DataTable = EjecutarConsultaDataTable()
        Me.Desconectar()
        Return dt
    End Function
    Public Function Delete(ByVal Cedula As String) As String
        Me.Conectar()
        Me.ComenzarTransaccion()
        Try
            Me.querystring = "update PERSONAS set ESTADO='IN' where CEDULA=:CEDULA"
            Me.CrearComando(querystring)
            Me.AsignarParametroCadena(":CEDULA", Cedula)
            Me.num_reg = EjecutarComando()
            Me.Msg = "Se realizo la Operacion Exitosamente - [" + num_reg.ToString + "] Filas Afectadas"
            Me.lErrorG = False
            Me.ConfirmarTransaccion()
        Catch ex As Exception
            CancelarTransaccion()
            Me.lErrorG = True
            Me.Msg = "Err: " + ex.Message
        Finally
            Me.Desconectar()
        End Try
        Return Msg
    End Function
End Class
